ODBC connection string

------=_Part_11512_31465453.1155071979490
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

Hi,

I'm using the following connection string to programatically link PostgreSQL
tables to MS Access.

strConn="ODBC;DRIVER={PostgreSQL};DATABASE=myDb;SERVER=192.1 68.0.91 ;" & _
"PORT=5432;UID=myDbUser;PWD=pass;A0=0;A1=7.4;A2=1;A3=0;A4=1; A5=0;A6=;A7=100;A8=4096;A9=0;"
& _
"B0=254;B1=8190;B2=0;B3=0;B4=1;B5=1;B6=0;B7=1;B8=0;B9=1;C0=0 ;C1=0;C2=dd_"

Dim tdf As TableDef

Set tdf = db.CreateTableDef(clients, dbAttachSavePWD, public.clients,
strConn)
db.TableDefs.Append tdf
db.TableDefs.Refresh

I can create the linked tables and insert rows into them. The problem is
tables without primary keys cannot be updated, though an insert is okay.

However, tables linked through a system DSN, have no updation problem. Is
there an error in the connection string? Where can one find the complete
list of parameters?

I don't want to create a DSN as I intend to distribute the Access file and
don't want savvy users to be able to access the tables through the DSN. I'm
not keen on creating a primary key for every table, either.

Is there a solution to this problem? Been looking for a one for several
days.

FYI, the PostgreSQL ODBC driver version is: 8.02.00.02

Thanks,
Biswajit


--
Call Toronto free: http://www.vbuzzer.com/index.php?aid=biswajit
Free SMS: http://www.atrochatro.com/ptnr.php?ptnr=12259

------=_Part_11512_31465453.1155071979490
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

Hi,

I'm using the following connection string to programatically link PostgreSQL tables to MS Access.

strConn="ODBC;DRIVER={PostgreSQL};DATABASE=myDb;SERVER=
192.168.0.91
;" & _
 " PORT=5432;UID=myDbUser;PWD=pass;A0=0;A1=7.4;A2=1;A3=0;A4=1;A 5=0;A6=;A7=100;A8=4096;A9=0; " & _
   " B0=254;B1=8190;B2=0;B3=0;B4=1;B5=1;B6=0;B7=1;B8=0;B9=1;C0=0; C1=0;C2=dd_ "



    Dim tdf As TableDef

    Set tdf = db.CreateTableDef(clients, dbAttachSavePWD, public.clients, strConn)
    db.TableDefs.Append tdf
    db.TableDefs.Refresh

I can create the linked tables and insert rows into them. The problem is tables without primary keys cannot be updated, though an insert is okay.


However, tables linked through a system DSN, have no updation problem. Is there an error in the connection string? Where can one find the complete list of parameters?

I don't want to create a DSN as I intend to distribute the Access file and don't want savvy users to be able to access the tables through the DSN. I'm not keen on creating a primary key for every table, either.


Is there a solution to this problem? Been looking for a one for several days.

FYI, the PostgreSQL ODBC driver version is:
8.02.00.02

Thanks,
Biswajit


--
Call Toronto free:

Free SMS:
http://www.atrochatro.com/ptnr.php?ptnr=12259



------=_Part_11512_31465453.1155071979490--

Re: ODBC connection string

--0__=0ABBFB57DFE5FB7A8f9e8a93df938690918c0ABBFB57DFE5FB7A
Content-type: multipart/alternative;
Boundary="1__=0ABBFB57DFE5FB7A8f9e8a93df938690918c0ABBFB57DF E5FB7A"

--1__=0ABBFB57DFE5FB7A8f9e8a93df938690918c0ABBFB57DFE5FB7A
Content-type: text/plain; charset="us-ascii"
Content-transfer-encoding: quoted-printable


The problem might not be your connection string.
I would recommended do some logging.
Try to do update while MyLog is enabled using the pgodbc and a DSN. (The
DSN is just a testing measure.). The ODBC database Administrator interface
gives you a means to set and reset MyLog.
Or log updates with ODBC Trace turned on.
Or even turn on command logging at the server by setting it postgresql.conf
and restarting the PG postmaster.

What I suspect is that without primary keys your Access has to identify
rows to update by using the long where clause
"WHERE field_a=3D'value_a' and field_b=3D'value_b' and field_c=3D'value_c',=
....".
Either because of case or some specific syntax issue, it cannot find the
record to update.

Either that or you might be using DAO to do an update like
rs.Edit
rs!field_a =3D "New value for A"
rs.Update
Here you have a recordset that you are holding the cursor open on across
statements,...a server side cursor,..whose support is always sketchy with
PostgreSQL.
If you had coded
sql =3D "UPDATE field_a=3D'new value for A' WHERE field_b=3D'value_b'.
CurrentDB.Execute sql
This simple type of Update statement should work. Of course you cannot
really do this is users are directly updating a table/tabular view of a
table or query, or bound form.

You seem to be using a good version of the ODBC driver. Perhaps the logging
will turn up some bug that needs to be addressed.

Detailed logging will tell you what statements are going to the PostgeSQL
database server engine,and what it is returning, as opposed to my
speculations.

Editorial Note: Primary keys change the interactions of interface and
database. It makes it possible to more quickly identify A record. Almost
all well designed tables have primary keys. Now I'll step off my soap box.




Greg Campbell ENG-ASE/Michelin US5
Lexington, South Carolina
803-951-5561, x75561
Fax: 803-951-5531
greg.campbell@us.michelin.com



=
=20
"Biswajit =
=20
Bardalai" =
=20
=20
> pgsql-odbc@postgresql.org =
=20
Sent by: cc=
=20
pgsql-odbc-owner@ =
=20
postgresql.org Subject=
=20
[ODBC] ODBC connection string =
=20
=
=20
08/08/2006 17:19 =
=20
=
=20
=
=20
=
=20
=
=20




Hi,

I'm using the following connection string to programatically link
PostgreSQL tables to MS Access.

strConn=3D"ODBC;DRIVER=3D{PostgreSQL};DATABASE=3DmyDb;SERVER =3D 192.168.0.9=
1 ;" & _

"PORT=3D5432;UID=3DmyDbUser;PWD=3Dpass;A0=3D0;A1=3D7.4;A2=3D 1;A3=3D0;A4=3D1=
;A5=3D0;A6=3D;A7=3D100;A8=3D4096;A9=3D0;"
& _

"B0=3D254;B1=3D8190;B2=3D0;B3=3D0;B4=3D1;B5=3D1;B6=3D0;B7=3D 1;B8=3D0;B9=3D1=
;C0=3D0;C1=3D0;C2=3Ddd_"

Dim tdf As TableDef

Set tdf =3D db.CreateTableDef(clients, dbAttachSavePWD, public.clients,
strConn)
db.TableDefs.Append tdf
db.TableDefs.Refresh

I can create the linked tables and insert rows into them. The problem is
tables without primary keys cannot be updated, though an insert is okay.

However, tables linked through a system DSN, have no updation problem. Is
there an error in the connection string? Where can one find the complete
list of parameters?

I don't want to create a DSN as I intend to distribute the Access file and
don't want savvy users to be able to access the tables through the DSN. I'm
not keen on creating a primary key for every table, either.

Is there a solution to this problem? Been looking for a one for several
days.

FYI, the PostgreSQL ODBC driver version is: 8.02.00.02

Thanks,
Biswajit


--
Call Toronto free: http://www.vbuzzer.com/index.php?aid=3Dbiswajit
Free SMS: http://www.atrochatro.com/ptnr.php?ptnr=3D12259

--1__=0ABBFB57DFE5FB7A8f9e8a93df938690918c0ABBFB57DFE5FB7A
Content-type: text/html; charset=US-ASCII
Content-Disposition: inline
Content-transfer-encoding: quoted-printable

The problem might not be your connection string. >

I would recommended do some logging.

Try to do update while MyLog is enabled using the pgodbc a=
nd a DSN. (The DSN is just a testing measure.). The ODBC database Administr=
ator interface gives you a means to set and reset MyLog.

Or log updates with ODBC Trace turned on.

Or even turn on command logging at the server by setting i=
t postgresql.conf and restarting the PG postmaster.



What I suspect is that without primary keys your Access h=
as to identify rows to update by using the long where clause

"WHERE field_a=3D'value_a' and field_b=3D'value_b' an=
d field_c=3D'value_c',...".

Either because of case or some specific syntax issue, it c=
annot find the record to update.



Either that or you might be using DAO to do an update like=


rs.Edit

rs!field_a =3D "New value for A"

rs.Update

Here you have a recordset that you are holding the cursor =
open on across statements,...a server side cursor,..whose support is always=
sketchy with PostgreSQL.

If you had coded

sql =3D "UPDATE field_a=3D'new value for A' WHERE fie=
ld_b=3D'value_b'.

CurrentDB.Execute sql

This simple type of Update statement should work. Of cours=
e you cannot really do this is users are directly updating a table/tabular =
view of a table or query, or bound form.



You seem to be using a good version of the ODBC driver. Pe=
rhaps the logging will turn up some bug that needs to be addressed.<=
br>


Detailed logging will tell you what statements are going t=
o the PostgeSQL database server engine,and what it is returning, as opposed=
to my speculations.



Editorial Note: Primary keys change the interactions of in=
terface and database. It makes it possible to more quickly identify A recor=
d. Almost all well designed tables have primary keys. Now I'll step off my =
soap box.









Greg Campbell ENG-ASE/Michelin US5

Lexington, South Carolina

803-951-5561, x75561

Fax: 803-951-5531

greg.campbell@us.michelin.com



6" height=3D"16" alt=3D"Inactive hide details for "Biswajit Bardalai&q=
uot; <bisbar@gmail.com>">"Biswajit Bardalai&quo=
t; <bisbar@gmail.com>

5FB7A8f9e8a93df9@michelin.com); background-repeat: no-repeat; " width=3D"40= %"> "Biswajit Bardalai" <bisbar@gmail.com&= gt; Sent by: pgsql-odbc-owner@postgresql.org 08/08/2006 17:19

8f9e8a93df9@michelin.com" border=3D"0" height=3D"1" width=3D"58" alt=3D""><= br> To "> =3D"0" height=3D"1" width=3D"1" alt=3D""> pgsql-odbc@postgresql.org 8f9e8a93df9@michelin.com" border=3D"0" height=3D"1" width=3D"58" alt=3D""><= br> cc "> =3D"0" height=3D"1" width=3D"1" alt=3D""> 8f9e8a93df9@michelin.com" border=3D"0" height=3D"1" width=3D"58" alt=3D""><= br> Subject "100%"> der=3D"0" height=3D"1" width=3D"1" alt=3D""> [ODBC] ODBC connection string 8f9e8a93df9@michelin.com" border=3D"0" height=3D"1" width=3D"1" alt=3D""> td> helin.com" border=3D"0" height=3D"1" width=3D"1" alt=3D"">